From 9a618c195631c2dc3fee0a85b970a712956c6668 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 31 Jul 2026 01:20:20 +0000 Subject: [PATCH] Create and document build_swelling_states() function --- db/schemas/sokwedb/functions/Makefile | 2 +- .../functions/create/build_swelling_states.m4 | 241 ++++++++++++++++++ .../functions/drop/build_swelling_states.m4 | 24 ++ doc/src/analyzed/swelling_states.m4 | 21 +- doc/src/code_tables.m4 | 7 +- doc/src/epilog.inc.m4 | 3 + doc/src/functions.m4 | 1 + doc/src/functions/build_swelling_states.m4 | 118 +++++++++ 8 files changed, 411 insertions(+), 6 deletions(-) create mode 100644 db/schemas/sokwedb/functions/create/build_swelling_states.m4 create mode 100644 db/schemas/sokwedb/functions/drop/build_swelling_states.m4 create mode 100644 doc/src/functions/build_swelling_states.m4 diff --git a/db/schemas/sokwedb/functions/Makefile b/db/schemas/sokwedb/functions/Makefile index c14f1b5..9351aa7 100644 --- a/db/schemas/sokwedb/functions/Makefile +++ b/db/schemas/sokwedb/functions/Makefile @@ -18,7 +18,7 @@ # Karl O. Pinc # This determines the order in which the functions are put into the database. -ORDER := build_arrivals_seq julian julian_to +ORDER := build_arrivals_seq build_swelling_states julian julian_to ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 new file mode 100644 index 0000000..526a32e --- /dev/null +++ b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 @@ -0,0 +1,241 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Rebuild ARRIVALS.Seq functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') +include(`constants.m4') +include(`macros.m4') +include(`functions.m4') +dnl + +CREATE OR REPLACE FUNCTION build_swelling_states() + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY INVOKER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute all SWELLING_STATES rows. + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_swelling_states() + -- + -- Returns: + -- The number of rows processed. + -- + -- Remarks: + + DECLARE + a_animid biography_data.animid%TYPE; + cnt INT := 0; + a_result INT := 0; + + BEGIN + FOR a_animid IN + SELECT biography_data.animid + FROM biography_data + WHERE biography_data.sex = 'sdb_female' + LOOP + SELECT build_swelling_states(a_animid) + INTO a_result; + cnt := cnt + a_result; + END LOOP; + + RETURN cnt; + END; +$$; + + +CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY INVOKER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute the ARRIVALS.Seq values of the ARRIVALS rows related to a + -- follow + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_swelling_states(animid) + -- + -- Input: + -- animid The BIOGRAPHY_DATA.AnimID of the individual who's + -- SWELLING_STATES rows are to be be re-built. + -- + -- Returns: + -- The number of rows processed. + -- + -- Remarks: + -- Likely not as efficient as it might be. We don't care. + + DECLARE + a_result INT := 0; + + a_date DATE; + + min_code cycle_states.code%TYPE; + max_code cycle_states.code%TYPE; + min_source swelling_sources.source%TYPE; + max_source swelling_sources.source%TYPE; + + min_manual swelling_sources.swellingmin%TYPE; + max_manual swelling_sources.swellingmin%TYPE; + + BEGIN + -- Delete all the old data + DELETE FROM swelling_states + WHERE swelling_states.animid = a_animid; + + -- Go through every date the individual has a swelling source + FOR a_date IN + SELECT obs.date + FROM obs + JOIN arrivals_a ON (arrivals_a.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + UNION + SELECT obs.date + FROM obs + JOIN arrivals ON (arrivals.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + UNION + SELECT obs.date + FROM obs + JOIN matings ON (matings.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + UNION + SELECT swelling_sources.date + FROM swelling_sources + WHERE swelling_sources.animid = a_animid + + LOOP + -- Create the SWELLING_STATES row for the day + WITH swellings (swelling_min, swelling_max, source) AS + -- Collect all the swellings and their sources + -- (except for manual sources) + (SELECT arrivals_a.swelling, arrivals_a.swelling + , 'sdb_sokwedb_source' + FROM obs + JOIN arrivals_a ON (arrivals_a.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + AND obs.date = a_date + UNION + SELECT arrivals.cycle, arrivals.cycle + , 'sdb_sokwedb_source' + FROM obs + JOIN arrivals ON (arrivals.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + AND obs.date = a_date + UNION + SELECT matings.swelling, matings.swelling + , 'sdb_sokwedb_source' + FROM obs + JOIN matings ON (matings.eid = obs.eid) + JOIN roles ON (roles.eid = obs.eid) + WHERE roles.participant = a_animid + AND obs.date = a_date + UNION + SELECT swelling_sources.swellingmin, swelling_sources.swellingmax + , swelling_sources.source + FROM swelling_sources + WHERE swelling_sources.animid = a_animid + AND swelling_sources.date = a_date + AND swelling_sources.source <> 'sdb_manual_source') + + -- Get minimum and maximum values + + , min_row AS + (SELECT swellings.swelling_min, swellings.source + FROM swellings + JOIN sighting_records + ON (sighting_records.code = swellings.source) + JOIN cycle_states + ON (cycle_states.code = swellings.swelling_min) + ORDER BY cycle_states.asnum + , sighting_records.priority + LIMIT 1) + + , max_row AS + (SELECT swellings.swelling_max, swellings.source + FROM swellings + JOIN sighting_records + ON (sighting_records.code = swellings.source) + JOIN cycle_states + ON (cycle_states.code = swellings.swelling_max) + ORDER BY cycle_states.asnum DESC + , sighting_records.priority + LIMIT 1) + + SELECT min_row.swelling_min, min_row.source + , max_row.swelling_max, max_row.source + INTO min_code, min_source + max_code, max_source + FROM min_row, max_row; + + -- See if there are manual overrides + SELECT swelling_sources.swellingmin, swelling_sources.swellingmax + INTO min_manual , max_manual + FROM swelling_sources + WHERE swelling_sources.date = a_date + AND swelling_sources.animid = a_animid + AND swelling_sources.source = 'sdb_manual_source'; + + IF FOUND THEN + -- There are manual values, use them when they are not NULL + IF min_manual IS NOT NULL THEN + min_code := min_manual; + min_source := 'sdb_manual_source'; + END IF; + + IF max_manual IS NOT NULL THEN + max_code := max_manual; + max_source := 'sdb_manual_source'; + END IF; + END IF; + + -- Save the row + INSERT INTO swelling_states + (date, animid + , swellingmin, swellingminsource + , swellingmax, swellingmaxsource) + VALUES (a_date, a_animid + , min_code, min_source + , max_code, max_source); + + a_result := a_result + 1; + END LOOP; + + RETURN a_result; + END; +$$; + + +grant_everybody_func_priv(`build_swelling_states()') +grant_everybody_func_priv(`build_swelling_states(TEXT)') diff --git a/db/schemas/sokwedb/functions/drop/build_swelling_states.m4 b/db/schemas/sokwedb/functions/drop/build_swelling_states.m4 new file mode 100644 index 0000000..ce5b0c0 --- /dev/null +++ b/db/schemas/sokwedb/functions/drop/build_swelling_states.m4 @@ -0,0 +1,24 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop ARRIVALS.Seq rebuilding functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') + +DROP FUNCTION IF EXISTS build_swelling_states(); +DROP FUNCTION IF EXISTS build_swelling_states(TEXT); diff --git a/doc/src/analyzed/swelling_states.m4 b/doc/src/analyzed/swelling_states.m4 index 7b79f2a..dd1c084 100644 --- a/doc/src/analyzed/swelling_states.m4 +++ b/doc/src/analyzed/swelling_states.m4 @@ -34,9 +34,12 @@ SWELLING_STATES This table provides an automatically maintained summary of the sexual swelling information found throughout SokweDB. -The SWELLING_STATES table is automatically constructed by the system and -cannot be manually maintained. -The table's rows are computed from the SokweDB tables' content. +The SWELLING_STATES table is constructed by the system and cannot be +manually maintained. +The table's rows are computed from the SokweDB tables' content by +executing the |function_build_swelling_states| function. +The |function_build_swelling_states| function must be manually +executed to update the rows in SWELLING_STATES.\ [#f1]_ The |SWELLING_SOURCES| table is used to both force specific sexual swelling values and to add additional records of sexual swelling @@ -96,6 +99,10 @@ without also being forced to supply manual value for |SWELLING_SOURCES.SwellingMin| and |SWELLING_SOURCES|.\ |SWELLING_SOURCES.SwellingMax| values are ignored. +For information on the columns within SokweDB from which swelling +state information is obtained, see the documentation of the +|function_build_swelling_states| function. + .. contents:: :depth: 2 @@ -199,3 +206,11 @@ See :ref:`above ` for more information on how the value of this column is determined. |notnull| + + +.. rubric:: Footnotes + +.. [#f1] + The system could be engineered to automatically re-construct + SWELLING_STATES as the data in SokweDB changes. + But this feature is not implemented. diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 639191b..509b3bc 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -1322,8 +1322,11 @@ sources recorded for a given individual on a given day in |SWELLING_STATES| uses a different method than |SIGHTINGS| does of determining what to record when there are multiple sources of information. -This means that the Priority value is *not* used to determine which -values in |SWELLING_SOURCES| appear in |SWELLING_STATES|. +This means that the Priority value is *not* the sole determinant of +which values in |SWELLING_SOURCES| appear in |SWELLING_STATES|. +For further detail see the documentation of +|function_build_swelling_states|. + |nonnegative| |notnull| diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 4dc9456..b762daa 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -930,6 +930,9 @@ elo_ranks_daily_rst(sdb_male, MT)dnl .. |function_build_arrivals_seq| replace:: :ref:`build_arrivals_seq() ` +.. |function_build_swelling_states| replace:: + :ref:`build_swelling_states() ` + .. |function_julian| replace:: :ref:`julian() ` .. |function_julian_to| replace:: :ref:`julian_to() ` diff --git a/doc/src/functions.m4 b/doc/src/functions.m4 index 30c8a42..e3b6add 100644 --- a/doc/src/functions.m4 +++ b/doc/src/functions.m4 @@ -35,5 +35,6 @@ users. :maxdepth: 1 functions/build_arrivals_seq.rst + functions/build_swelling_states.rst functions/julian.rst functions/julian_to.rst diff --git a/doc/src/functions/build_swelling_states.m4 b/doc/src/functions/build_swelling_states.m4 new file mode 100644 index 0000000..2e37c09 --- /dev/null +++ b/doc/src/functions/build_swelling_states.m4 @@ -0,0 +1,118 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + + +.. _function_build_swelling_states: + +build_swelling_states() -- Rebuild the |SWELLING_STATES| table +-------------------------------------------------------------- + +Synopsis +```````` + +:: + + build_swelling_states() INT + build_swelling_states(animid TEXT) INT + +Input +````` + +animid +'''''' + +A |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value, designating the +individual which is to have its |SWELLING_STATES| rows recomputed. + +This value is not validated. +The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of a male, or any other +individual with no swelling data, or even a non-existent +|BIOGRAPHY_DATA.AnimID|, may be specified and the function will +execute without error. + +The function's return value may be examined to determine whether +changes were made to |SWELLING_STATES|. + + +Description +``````````` + +.. |build_swelling_states_summary| replace:: + Rebuild the |SWELLING_STATES| table's rows, either all of them or + those of only one individual. + +|build_swelling_states_summary| + +When this function is called without an argument, it re-computes the +entire |SWELLING_STATES| table. +This is done by examining all data associated with all +|BIOGRAPHY_DATA| rows having a ``sdb_female`` |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.Sex| value. + +When called with a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value, +it re-computes the |SWELLING_STATES| rows belonging to the given +individual. + +Re-computation deletes those rows on |SWELLING_STATES| associated with +the individual(s) who's swelling states were re-computed. + +The columns within SokweDB from which swelling information is obtained +are: + +* |ARRIVALS_A|.\ |ARRIVALS_A.Swelling| + +* |ARRIVALS|.\ |ARRIVALS.Cycle|\ + +* |MATINGS|.\ |MATINGS.Swelling| + +* |SWELLING_SOURCES|.\ |SWELLING_SOURCES.SwellingMax| + +* |SWELLING_SOURCES|.\ |SWELLING_SOURCES.SwellingMin| + +When the minimum or maximum value appears in more than one of the +above tables, the source recorded in |SWELLING_SOURCES|.\ +|SWELLING_SOURCES.Source| is that with the smallest +|SIGHTING_RECORDS|.\ |SIGHTING_RECORDS.Priority| value. + + +Examples +```````` + +.. code-block:: sql + :caption: + Rebuild SWELLING_STATES for the individual with the AnimID of "JANE" + + SELECT build_swelling_states('JANE'); + + +.. code-block:: sql + :caption: + Rebuild the entire SWELLING_STATES table + + SELECT build_swelling_states(); + + + +Return Value +```````````` + +The function returns the number of rows computed, regardless of +how many rows previously existed in |SWELLING_STATES|. -- 2.34.1